import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';

import '../models/fold.dart';
import '../models/note.dart';

class DatabaseService {
  // 单例实例
  static final DatabaseService _instance = DatabaseService._internal();
  factory DatabaseService() => _instance;
  static final int _version = 2;
  DatabaseService._internal();

  // 数据库实例和状态
  static Database? _database;
  bool _isInitialized = false;


  // 获取数据库实例（懒加载）
  Future<Database> get database async {
    if (!_isInitialized) {
      await _initDatabase();
      _isInitialized = true;
    }
    return _database!;
  }

  // 初始化数据库
  Future<void> _initDatabase() async {
    // 获取设备存储路径
    final documentsDirectory = await getApplicationDocumentsDirectory();
    final path = join(documentsDirectory.path, 'notes.db');

    // 打开/创建数据库
    _database = await openDatabase(
      path,
      version: _version,
      onCreate: _createDB, // 创建表
      onUpgrade: _upgrade
    );
  }

  Future<void> _createDB(Database db, int version) async {
    await db.execute('''
      CREATE TABLE notes (
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        content TEXT NOT NULL,
        dateCreated TEXT NOT NULL,
        dateModified TEXT,
        foldId TEXT
      )
    ''');

    await db.execute('''
      CREATE TABLE fold (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL
      )
    ''');
  }

  Future<void> _upgrade(Database db, int oldVersion, int newVersion)async {
    if(oldVersion<2){
      await db.execute('ALTER TABLE notes RENAME COLUMN tags TO foldId');
    }
  }

  // 添加按标签搜索的方法
  Future<List<Note>> readNotesByFold(String fold) async {
    final db =  _database!;
    final result = await db.query(
      'notes',
      where: 'foldId=?',
      whereArgs: [fold],
      orderBy: 'dateCreated DESC',
    );
    return result.map((json) => Note.fromMap(json)).toList();
  }


  Future<void> createNote(Note note) async {
    final db = _database!;
    await db.insert('notes', note.toMap());
  }

  Future<void> createFold(Fold fold) async {
    final db = _database!;
    await db.insert('fold', fold.toMap());
  }


  Future<int> updateFold(Fold fold) async {
    final db = _database!;
    return await db.update(
      'fold',
      fold.toMap(),
      where: 'id = ?',
      whereArgs: [fold.id],
    );
  }

  Future<List<Note>> readAllNotes() async {
    final db = _database!;
    final result = await db.query('notes', orderBy: 'dateCreated DESC');
    return result.map((json) => Note.fromMap(json)).toList();
  }

  Future<List<Fold>> readAllFolds() async {
    final db = _database!;
    final result = await db.query('fold');
    return result.map((json) =>Fold.fromMap(json)).toList();
  }



  Future<Note> readNote(String id) async {
    final db = _database!;
    final maps = await db.query(
      'notes',
      where: 'id = ?',
      whereArgs: [id],
    );

    if (maps.isNotEmpty) {
      return Note.fromMap(maps.first);
    } else {
      throw Exception('ID $id not found');
    }
  }

  Future<int> updateNote(Note note) async {
    final db = _database!;
    return await db.update(
      'notes',
      note.toMap(),
      where: 'id = ?',
      whereArgs: [note.id],
    );
  }

  Future<int> deleteNote(String id) async {
    final db = _database!;
    return await db.delete(
      'notes',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  updateNotes(List<Note> notes) async {
    final db = _database!;
    await db.transaction((txn) async {
      for (var item in notes) {
        await txn.update(
          'notes',
          item.toMap(),
          where: 'id = ?',
          whereArgs: [item.id],
        );
      }
    });
  }

  delFold(List<String> ids){
    final db = _database!;
    return db.delete(
      'fold',
      where: 'id IN (${List.filled(ids.length, '?').join(',')})',
      whereArgs: ids,
    );
  }

  Future close() async {
    final db = _database!;
    db.close();
  }
}